#include "sql.h"

SqlHelper& SqlHelper::getInstance()
{
    static SqlHelper inst;
    return inst;
}

SqlHelper::SqlHelper()
{
      createConnection();
//
}
SqlHelper::~SqlHelper()
{
//
}

void SqlHelper::createConnection()
{
    db = QSqlDatabase::addDatabase("QSQLITE");
    db.setDatabaseName("evaluate.db");
    if (!db.open()) {
        qDebug() << "Cannot open database:" << db.lastError();
    } else {
        qDebug() << "Create database succ";
    }
}

void SqlHelper::createTables()
{
    QString createTeachersTable = R"(
        CREATE TABLE IF NOT EXISTS teachers (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            name TEXT NOT NULL,
            college TEXT NOT NULL,
            department TEXT NOT NULL,
            title TEXT NOT NULL
        )
    )";
    QString createCoursesTable = R"(
        CREATE TABLE IF NOT EXISTS courses (
            code TEXT PRIMARY KEY,
            name TEXT NOT NULL,
            type TEXT NOT NULL,
            semester TEXT NOT NULL,
            credits INTEGER NOT NULL,
            student_count INTEGER NOT NULL,
            teacher_name TEXT NOT NULL
        )
    )";
    QString createEvaluationsTable = R"(
        CREATE TABLE IF NOT EXISTS evaluations (
            course_code TEXT NOT NULL,
            self_evaluation INTEGER NOT NULL,
            student_evaluation INTEGER NOT NULL,
            supervisor_evaluation INTEGER NOT NULL,
            peer_evaluation INTEGER NOT NULL,
            FOREIGN KEY (course_code) REFERENCES courses(code)
        )
    )";
    QString createStudentsTable = R"(
        CREATE TABLE IF NOT EXISTS students (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            name TEXT NOT NULL,
            enrolled_courses TEXT NOT NULL
        )
    )";
    QString createUsersTable = R"(
        CREATE TABLE IF NOT EXISTS users (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            username TEXT NOT NULL UNIQUE,
            password TEXT NOT NULL,
            user_type TEXT NOT NULL
        )
    )";
    QString createWeightsTable = R"(
        CREATE TABLE IF NOT EXISTS evaluation_weights (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            evaluation_type TEXT NOT NULL UNIQUE,
            weight REAL NOT NULL
        )
    )";

    QSqlQuery query(db);
    if (!query.exec(createTeachersTable)) {
        qDebug() << "Failed to create teachers table:" << query.lastError();
    }
    if (!query.exec(createCoursesTable)) {
        qDebug() << "Failed to create courses table:" << query.lastError();
    }
    if (!query.exec(createEvaluationsTable)) {
        qDebug() << "Failed to create evaluations table:" << query.lastError();
    }
    if (!query.exec(createStudentsTable)) {
        qDebug() << "Failed to create students table:" << query.lastError();
    }
    if (!query.exec(createUsersTable)) {
        qDebug() << "Failed to create users table:" << query.lastError();
    }
    if (!query.exec(createWeightsTable)) {
        qDebug() << "Failed to create evaluation_weights table:" << query.lastError();
    }


    if (insertUser("杨宇", "1234", "student")) {
        qDebug() << "Successfully inserted user.";
    }
    if (insertStudent("杨宇", "CS101, MATH202, ENG102, MATH203, THK204")) {
        qDebug() << "Successfully inserted student.";
    }

    if (insertUser("刘丹", "1234", "teacher")) {
        qDebug() << "Successfully inserted user.";
    }
    if (insertTeacher("刘丹", "计算机学院", "Computer Science", "teacher")) {
        qDebug() << "Successfully inserted teacher.";
    }

    if (insertUser("方虎", "1234", "teacher")) {
        qDebug() << "Successfully inserted user.";
    }
    if (insertTeacher("方虎", "计算机学院", "Computer Science", "teacher")) {
        qDebug() << "Successfully inserted teacher.";
    }

    if (insertUser("刘璐", "1234", "supervisor")) {
        qDebug() << "Successfully inserted user.";
    }

    if (insertCourse("CS101", "计算机导论", "Core", "2020年下半学期", 4, 30, "刘丹")) {
        qDebug() << "Successfully inserted course.";
    }
    if (insertCourse("MATH202", "高等数学", "Core", "2020年下半学期", 4, 30, "方虎")) {
        qDebug() << "Successfully inserted course.";
    }
    if (insertCourse("ENG102", "大学英语", "Core", "2020年下半学期", 4, 30, "刘丹")) {
        qDebug() << "Successfully inserted course.";
    }
    if (insertCourse("MATH203", "线性代数", "Core", "2020年下半学期", 4, 30, "方虎")) {
        qDebug() << "Successfully inserted course.";
    }
    if (insertCourse("THK204", "思想道德与发展", "Core", "2020年下半学期", 4, 30, "方虎")) {
        qDebug() << "Successfully inserted course.";
    }

    if (insertEvaluationWeight("self", 1)
     && insertEvaluationWeight("student", 2)
     && insertEvaluationWeight("supervisor", 3)
     && insertEvaluationWeight("peer", 1)) {
        qDebug() << "Successfully inserted evaluation weights";
    }

}

void SqlHelper::deleteDataBase()
{
    if (db.isValid()) {
        db.close();
        QSqlDatabase::removeDatabase("evaluate.db");
    }
    if (QFile::exists("evaluate.db")) {
            if (QFile::remove("evaluate.db")) {
                qDebug() << "Database file deleted successfully.";
            } else {
                qDebug() << "Failed to delete database file.";
            }
        } else {
            qDebug() << "Database file does not exist.";
    }
}

bool SqlHelper::insertTeacher(const QString &name, const QString &college, const QString &department, const QString &title) {
    QSqlQuery query(db);
    query.prepare("INSERT INTO teachers (name, college, department, title) VALUES (?, ?, ?, ?)");
    query.addBindValue(name);
    query.addBindValue(college);
    query.addBindValue(department);
    query.addBindValue(title);
    if (!query.exec()) {
        qDebug() << "Failed to insert teacher:" << query.lastError();
        return false;
    }
    return true;
}

QList<QVariantList> SqlHelper::queryTeachers() {
    QList<QVariantList> results;
    QSqlQuery query(db);
    if (query.exec("SELECT id, name, college, department, title FROM teachers")) {
        while (query.next()) {
            QVariantList row;
            for (int i = 0; i < 5; ++i) {
                row.append(query.value(i));
            }
            results.append(row);
        }
    } else {
        qDebug() << "Failed to query teachers:" << query.lastError();
    }
    return results;
}

QVariantList SqlHelper::queryTeacherByName(const QString &name) {
    QVariantList results;
    QSqlQuery query(db);
    QString queryStr = R"(SELECT id, name, college, department, title FROM teachers
                          WHERE name = ?)";
    query.prepare(queryStr);
    query.addBindValue(name);
    if (query.exec()) {
        if (query.next()) {  // 确保移动到结果的第一行
            for (int i = 0; i < 5; ++i) {
                results.append(query.value(i));
            }
        }
    }
    else {
        qDebug() << "Failed to query teacher:" << query.lastError();
    }
    return results;
}

bool SqlHelper::insertCourse(const QString &code, const QString &name, const QString &type, const QString &semester, int credits, int studentCount, const QString &teacherName) {
    QSqlQuery query(db);
    query.prepare("INSERT INTO courses (code, name, type, semester, credits, student_count, teacher_name) VALUES (?, ?, ?, ?, ?, ?, ?)");
    query.addBindValue(code);
    query.addBindValue(name);
    query.addBindValue(type);
    query.addBindValue(semester);
    query.addBindValue(credits);
    query.addBindValue(studentCount);
    query.addBindValue(teacherName);
    if (!query.exec()) {
        qDebug() << "Failed to insert course:" << query.lastError();
        return false;
    }
    return true;
}

QList<QVariantList> SqlHelper::queryCourses() {
    QList<QVariantList> results;
    QSqlQuery query(db);
    if (query.exec("SELECT code, name, type, semester, credits, student_count, teacher_name FROM courses")) {
        while (query.next()) {
            QVariantList row;
            for (int i = 0; i < 7; ++i) {
                row.append(query.value(i));
            }
            results.append(row);
        }
    } else {
        qDebug() << "Failed to query courses:" << query.lastError();
    }
    return results;
}

QList<QPair<QString, QString> > SqlHelper::queryCoursesForUser(const QString &username)
{
    QString queryStr = R"(
        SELECT c.code, c.name
        FROM courses c
        JOIN students s ON s.enrolled_courses LIKE '%' || c.code || '%'
        WHERE s.name = ?
    )";
    QSqlQuery query(db);
    query.prepare(queryStr);
    query.addBindValue(username);

    QList<QPair<QString, QString>> courseList;

    if (query.exec())
    {
        while (query.next()) {
            QString courseCode = query.value(0).toString();
            QString courseName = query.value(1).toString();
            courseList.append(qMakePair(courseName, courseCode));
        }
    }
    else
        qDebug() << "Failed to query courses for user:" << query.lastError();

    return courseList;
}

QVariantList SqlHelper::queryCourseByCourseCode(const QString &courseCode) {
    QVariantList results;
    QSqlQuery query(db);
    QString queryStr = R"(SELECT name, type, semester, credits, student_count, teacher_name
                          FROM courses
                          WHERE code = ?)";
    query.prepare(queryStr);
    query.addBindValue(courseCode);
    if (query.exec()) {
        if (query.next()) {  // 确保移动到结果的第一行
            for (int i = 0; i < 6; ++i) {
                results.append(query.value(i));
            }
        }
    } else {
        qDebug() << "Failed to query course:" << query.lastError();
    }
    return results;
}


bool SqlHelper::insertEvaluation(const QString &courseCode, int selfEvaluation, int studentEvaluation, int supervisorEvaluation, int peerEvaluation) {
    QSqlQuery query(db);
    query.prepare("INSERT INTO evaluations (course_code, self_evaluation, student_evaluation, supervisor_evaluation, peer_evaluation) VALUES (?, ?, ?, ?, ?)");
    query.addBindValue(courseCode);
    query.addBindValue(selfEvaluation);
    query.addBindValue(studentEvaluation);
    query.addBindValue(supervisorEvaluation);
    query.addBindValue(peerEvaluation);
    if (!query.exec()) {
        qDebug() << "Failed to insert evaluation:" << query.lastError();
        return false;
    }
    return true;
}

QList<QVariantList> SqlHelper::queryEvaluations() {
    QList<QVariantList> results;
    QSqlQuery query(db);
    if (query.exec("SELECT course_code, self_evaluation, student_evaluation, supervisor_evaluation, peer_evaluation FROM evaluations")) {
        while (query.next()) {
            QVariantList row;
            for (int i = 0; i < 5; ++i) {
                row.append(query.value(i));
            }
            results.append(row);
        }
    } else {
        qDebug() << "Failed to query evaluations:" << query.lastError();
    }
    return results;
}

QList<QVariantList> SqlHelper::queryEvaluationsForTeacher(const QString &teacherName) {
    QList<QVariantList> evaluations;
    QSqlQuery query(db);

    QString queryString = R"(
        SELECT e.self_evaluation, e.student_evaluation, e.supervisor_evaluation, e.peer_evaluation
        FROM evaluations e
        JOIN courses c ON e.course_code = c.code
        WHERE c.teacher_name = ?
    )";

    query.prepare(queryString);
    query.addBindValue(teacherName);

    if (query.exec()) {
        while (query.next()) {
            QVariantList row;
            for (int i = 0; i < 4; ++i) { // 假设有四项需要计算
                row.append(query.value(i));
            }
            evaluations.append(row);
        }
    } else {
        qDebug() << "Failed to query evaluations for teacher:" << query.lastError();
    }

    return evaluations;
}

bool SqlHelper::insertStudent(const QString &name, const QString &enrolledCourses) {
    QSqlQuery query(db);
    query.prepare("INSERT INTO students (name, enrolled_courses) VALUES (?, ?)");
    query.addBindValue(name);
    query.addBindValue(enrolledCourses);
    if (!query.exec()) {
        qDebug() << "Failed to insert student:" << query.lastError();
        return false;
    }
    return true;
}

QList<QVariantList> SqlHelper::queryStudents() {
    QList<QVariantList> results;
    QSqlQuery query(db);
    if (query.exec("SELECT name, enrolled_courses FROM students")) {
        while (query.next()) {
            QVariantList row;
            for (int i = 0; i < 2; ++i) {
                row.append(query.value(i));
            }
            results.append(row);
        }
    } else {
        qDebug() << "Failed to query students:" << query.lastError();
    }
    return results;
}

bool SqlHelper::insertUser(const QString &username, const QString &password, const QString &userType) {
    QSqlQuery query(db);
    query.prepare("INSERT INTO users (username, password, user_type) VALUES (?, ?, ?)");
    query.addBindValue(username);
    query.addBindValue(password);
    query.addBindValue(userType);
    if (!query.exec()) {
        qDebug() << "Failed to insert user:" << query.lastError();
        return false;
    }
    return true;
}
QList<QVariantList> SqlHelper::queryUsers() {
    QList<QVariantList> results;
    QSqlQuery query(db);
    if (query.exec("SELECT username, password, user_type FROM users")) {
        while (query.next()) {
            QVariantList row;
            for (int i = 0; i < 3; ++i) {
                row.append(query.value(i));
            }
            results.append(row);
        }
    } else {
        qDebug() << "Failed to query users:" << query.lastError();
    }
    return results;
}

bool SqlHelper::insertEvaluationWeight(const QString& evaluationType, double weight) {
    QSqlQuery query(db);
    query.prepare("INSERT INTO evaluation_weights (evaluation_type, weight) VALUES (?, ?)");
    query.addBindValue(evaluationType);
    query.addBindValue(weight);
    if (!query.exec()) {
        qDebug() << "Failed to insert evaluation weight:" << query.lastError();
        return false;
    }
    return true;
}

QMap<QString, double> SqlHelper::queryEvaluationWeights() const {
    QMap<QString, double> weights;
    QSqlQuery query(db);
    if (query.exec("SELECT evaluation_type, weight FROM evaluation_weights")) {
        while (query.next()) {
            QString evaluationType = query.value(0).toString();
            double weight = query.value(1).toDouble();
            weights.insert(evaluationType, weight);
        }
    } else {
        qDebug() << "Failed to query evaluation weights:" << query.lastError();
    }
    return weights;
}

bool SqlHelper::updateEvaluation(const QString &courseCode, const QString &evaluationType, double evaluationValue) {
    QSqlQuery query(db);
    query.prepare("SELECT self_evaluation, student_evaluation, supervisor_evaluation, peer_evaluation FROM evaluations WHERE course_code = ?");
    query.addBindValue(courseCode);

    if (!query.exec()) {
        qDebug() << "Failed to query evaluation:" << query.lastError();
        return false;
    }

    int index = -1;

    // 确定要更新的列索引
    if (evaluationType == "self_evaluation") {
        index = 0;
    } else if (evaluationType == "student_evaluation") {
        index = 1;
    } else if (evaluationType == "supervisor_evaluation") {
        index = 2;
    } else if (evaluationType == "peer_evaluation") {
        index = 3;
    } else {
        qDebug() << "Invalid evaluation type";
        return false;
    }

    if (query.next()) {
        int currentEvalValue = query.value(index).toInt();

        query.prepare(QString("UPDATE evaluations SET %1 = ? WHERE course_code = ?")
                      .arg(evaluationType));
        query.addBindValue(evaluationValue);
        query.addBindValue(courseCode);

        if (!query.exec()) {
            qDebug() << "Failed to update evaluation:" << query.lastError();
            return false;
        }

        qDebug() << "Updated evaluation type" << evaluationType
                 << "for course" << courseCode << "from" << currentEvalValue
                 << "to" << evaluationValue;

    } else {
        query.prepare("INSERT INTO evaluations (course_code, self_evaluation, student_evaluation, supervisor_evaluation, peer_evaluation) "
                      "VALUES (?, ?, ?, ?, ?)");

        // 初始化所有评教字段，除了指定的
        query.addBindValue(courseCode);
        query.addBindValue((evaluationType == "self") ? evaluationValue : 0);
        query.addBindValue((evaluationType == "student") ? evaluationValue : 0);
        query.addBindValue((evaluationType == "supervisor") ? evaluationValue : 0);
        query.addBindValue((evaluationType == "peer") ? evaluationValue : 0);

        if (!query.exec()) {
            qDebug() << "Failed to insert evaluation:" << query.lastError();
            return false;
        }

        qDebug() << "Inserted evaluation for course" << courseCode;
    }
    return true;
}


